using System;
using System.Collections;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

using LocalBoard.Domain;

namespace LocalBoard.Persistence.SqlAccessor
{
	/// <summary>
	/// Summary description for SqlUserRoleAccessor.
	/// </summary>
	public class SqlUserRoleAccessor : SqlDataAccessBase, IUserRoleAccessor, IClubRoleAccessor
	{
		public static readonly SqlUserRoleAccessor Instance = new SqlUserRoleAccessor();
		private SqlAccessorFactory _accessorFactory = new SqlAccessorFactory();

		private SqlUserRoleAccessor()
		{
		}

		#region IUserRoleAccessor Members

		public UserRole Get(int id)
		{
			SqlDataReader dataReader = null;
			try
			{
				dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetUserRoleById", id);
				// Execute the stored procedure
				if(dataReader.Read())
				{
					return new UserRole(
						DataConverter.GetInteger(dataReader["Id"]),
						DataConverter.GetString(dataReader["Name"])
						);
				}
			}
			catch (Exception ex )
			{
				// Log error details
				throw ex;
			}
			finally
			{
				if(dataReader != null) dataReader.Close();
			}
			return null;
		}
		public System.Collections.Hashtable GetAllUserRoles()
		{
			using (SqlDataReader dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetAllUserRoles"))
			{
				Hashtable roles = new Hashtable();
				while(dataReader.Read())
				{
					roles.Add(DataConverter.GetInteger(dataReader["Id"]), new UserRole(DataConverter.GetInteger(dataReader["Id"]), DataConverter.GetString(dataReader["Name"])));
				}
				return roles;
			}
		}

		public System.Collections.ArrayList GetRolesByProfileId(int profileId)
		{
			using (SqlDataReader dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetUserRolesByProfileId", profileId))
			{
				ArrayList roles = new ArrayList();
				while(dataReader.Read())
				{
					roles.Add(new UserRole(DataConverter.GetInteger(dataReader["Id"]), DataConverter.GetString(dataReader["Name"])));
				}
				return roles;
			}
		}

		public int Insert(string userRoleName)
		{
			int returnVal = Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, "InsertUserRole", userRoleName));
			if(returnVal == -100)
				throw new ApplicationException("Duplicate name");
			return returnVal;
		}

		public void Update(UserRole userRole)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "UpdateUserRole", userRole.Id, userRole.Name);
		}

		public void Delete(int id)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "DeleteUserRole", id);
		}

		public void AddRoleToProfile(int roleId, int profileId)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "AddRoleToProfile", roleId, profileId);
		}

		public void DeleteRoleFromProfile(int roleId, int profileId)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "DeleteRoleFromProfile", roleId, profileId);
		}

		#endregion

		#region IClubRoleAccessor Members

		/// <summary>
		/// Adding club role to club member's profile
		/// </summary>
		/// <param name="roleId">club role id</param>
		/// <param name="profileId">club memeber's profile id</param>
		/// <param name="clubId">club id</param>
		public void AddClubRoleToProfile(int roleId, int profileId, int clubId)
		{
			if(_accessorFactory.GetClubRoleAccessor().Get(roleId, clubId) == null)
				throw new ArgumentException("Role id, \"" + roleId.ToString() + "\" is not in the club.", "roleId");
			if(!_accessorFactory.GetClubProfileAccessor().IsUserInTheClub(profileId, clubId))
				throw new ArgumentException("Profile id, \"" + profileId.ToString() + "\" is not in the club.", "profileId");
			SqlHelper.ExecuteNonQuery(ConnectionString, "AddClubRoleToProfile", roleId, profileId, clubId);
		}

		/// <summary>
		/// Delete club role
		/// </summary>
		/// <param name="id">club role id</param>
		/// <param name="clubId">club id</param>
		public void Delete(int id, int clubId)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "DeleteClubRole", id, clubId);
		}

		/// <summary>
		/// Delete club role from club memeber's profile
		/// </summary>
		/// <param name="roleId">club role id</param>
		/// <param name="profileId">club memeber's profile id</param>
		/// <param name="clubId">club id</param>
		public void DeleteClubRoleFromProfile(int roleId, int profileId, int clubId)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "DeleteClubRoleFromProfile", roleId, profileId, clubId);
		}

		/// <summary>
		/// Get club role by role id
		/// </summary>
		/// <param name="id">club role id</param>
		/// <param name="clubId">club id</param>
		/// <returns></returns>
		public UserRole Get(int id, int clubId)
		{
			SqlDataReader dataReader = null;
			try
			{
				dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetClubRoleById", id, clubId);
				// Execute the stored procedure
				if(dataReader.Read())
				{
					return new UserRole(
						DataConverter.GetInteger(dataReader["Id"]),
						DataConverter.GetString(dataReader["Name"])
						);
				}
			}
			catch (Exception ex )
			{
				// Log error details
				throw ex;
			}
			finally
			{
				if(dataReader != null) dataReader.Close();
			}
			return null;
		}

		/// <summary>
		/// Get club role by role name
		/// </summary>
		/// <param name="roleName">club role name</param>
		/// <param name="clubId">club id</param>
		/// <returns></returns>
		public UserRole Get(string roleName, int clubId)
		{
			foreach(DictionaryEntry entry in GetAllClubRoles(clubId))
			{
				UserRole role = entry.Value as UserRole;
				if(role.Name == roleName)
					return role;
			}
			return null;
		}

		/// <summary>
		/// Get all club roles
		/// </summary>
		/// <param name="clubId">club id</param>
		/// <returns>all roles in club as Hashtable</returns>
		public Hashtable GetAllClubRoles(int clubId)
		{
			using (SqlDataReader dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetAllClubRoles", clubId))
			{
				Hashtable roles = new Hashtable();
				while(dataReader.Read())
				{
					roles.Add(DataConverter.GetInteger(dataReader["Id"]), new UserRole(DataConverter.GetInteger(dataReader["Id"]), DataConverter.GetString(dataReader["Name"])));
				}
				return roles;
			}
		}

		/// <summary>
		/// Get all the roles that club member has
		/// </summary>
		/// <param name="profileId">memeber's profile id</param>
		/// <param name="clubId">club id</param>
		/// <returns>all the roles as ArrayList</returns>
		public ArrayList GetClubRolesByProfileId(int profileId, int clubId)
		{
			using (SqlDataReader dataReader = SqlHelper.ExecuteReader(ConnectionString, "GetClubRolesByProfileId", profileId, clubId))
			{
				ArrayList roles = new ArrayList();
				while(dataReader.Read())
				{
					roles.Add(new UserRole(DataConverter.GetInteger(dataReader["Id"]), DataConverter.GetString(dataReader["Name"])));
				}
				return roles;
			}
		}

		/// <summary>
		/// Insert club role
		/// </summary>
		/// <param name="userRoleName">club role name</param>
		/// <param name="clubId">club id</param>
		/// <returns>inserted role's id</returns>
		public int Insert(string userRoleName, int clubId)
		{
			if(Get(userRoleName, clubId) != null)
				throw new ApplicationException(string.Format("Duplicate role name, \"{0}\" in the club id, \"{1}\"", userRoleName, clubId.ToString()));
			return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, "InsertClubRole", userRoleName, clubId));
		}

		/// <summary>
		/// Update club role
		/// </summary>
		/// <param name="userRole">role that has information to update</param>
		/// <param name="clubId">club id</param>
		public void Update(UserRole userRole, int clubId)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString, "UpdateClubRole", userRole.Id, userRole.Name, clubId);
		}

		#endregion
	}
}
